package com.mamba.microservice.document.excel;

import com.mamba.microservice.domain.ExcelHeadDomain;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * Copyright ©2019 360jinrong All Rights Reserved
 * 请添加类/接口的说明：
 *
 * @Package: com.qihoo.finance.utils
 * @author: fangjin
 * @date: 2019年10月31日 18时06分
 */
public class ExcelUtils {
    public static final String OFFICE_EXCEL_XLSX = "xlsx";

    public static <T> void writeEntityToExcel(List<T> values, OutputStream os, List<ExcelHeadDomain> excelHeadDomains, String sheetName) throws Exception {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //创建标题
        for (int i = 0; i < excelHeadDomains.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(excelHeadDomains.get(i).getExcelName());
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, excelHeadDomains.get(i).getColumnWidth());
        }
        //创建内容
        for (int i = 0; i < values.size(); i++) {
            row = sheet.createRow(i + 1);
            T t = values.get(i);
            for (int j = 0; j < excelHeadDomains.size(); j++) {
                ExcelHeadDomain excelHeadDomain = excelHeadDomains.get(j);
                Class clazz = t.getClass();
                flag:
                while (clazz != null) {
                    Field[] fields = clazz.getDeclaredFields();
                    for (Field field : fields) {
                        if (excelHeadDomain.getEntityName().equalsIgnoreCase(field.getName())) {
                            String methodName = MethodUtils.getMethodName(field.getName());
                            Method method = clazz.getDeclaredMethod(methodName);
                            String value = String.valueOf(method.invoke(t));
                            if (StringUtils.isEmpty(value)) {
                                break flag;
                            }
                            XSSFCell cell = row.createCell(j);
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(value);
                            break flag;
                        }
                    }
                    clazz = clazz.getSuperclass();
                }
            }
        }
        wb.write(os);
    }

    /**
     * Excel表头对应Entity属性 解析封装javabean
     *
     * @param clazz          类
     * @param in             excel流
     * @param fileNameSuffix 文件名后缀
     * @param excelHeadDomains     excel表头与entity属性对应关系
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> clazz, InputStream in, String fileNameSuffix, List<ExcelHeadDomain> excelHeadDomains, String sheetName) throws Exception {
        Workbook workbook = getWorkBoot(in, fileNameSuffix);
        List<T> excelForBeans = readExcel(clazz, workbook, excelHeadDomains, sheetName);
        return excelForBeans;
    }

    private static Workbook getWorkBoot(InputStream in, String fileNameSuffix) throws IOException {
        if (OFFICE_EXCEL_XLSX.equals(fileNameSuffix)) {
            return new XSSFWorkbook(in);
        } else {
            return new HSSFWorkbook(in);
        }
    }

    private static <T> List<T> readExcel(Class<T> clazz, Workbook workbook, List<ExcelHeadDomain> excelHeadDomains, String sheetName) throws Exception {
        List<T> beans = new ArrayList<T>();
        Sheet sheet = workbook.getSheet(sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        Row head = sheet.getRow(firstRowNum);
        short firstCellNum = head.getFirstCellNum();
        short lastCellNum = head.getLastCellNum();

        for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
            Row dataRow = sheet.getRow(rowIndex);
            if (dataRow == null) {
                continue;
            }

            if (CollectionUtils.isEmpty(excelHeadDomains)) {
                firstCellNum = dataRow.getFirstCellNum();
                lastCellNum = dataRow.getLastCellNum();
            }
            T instance = clazz.newInstance();
            for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
                Class dataClazz = clazz;
                Cell headCell = head.getCell(cellIndex);
                if (headCell == null) {
                    continue;
                }
                Cell cell = dataRow.getCell(cellIndex);
                headCell.setCellType(Cell.CELL_TYPE_STRING);
                String headName = headCell.getStringCellValue().trim();
                if (StringUtils.isEmpty(headName)) {
                    continue;
                }
                ExcelHeadDomain eHead = null;
                if (!CollectionUtils.isEmpty(excelHeadDomains)) {
                    for (ExcelHeadDomain excelHeadDomain : excelHeadDomains) {
                        if (headName.equals(excelHeadDomain.getExcelName())) {
                            eHead = excelHeadDomain;
                            headName = eHead.getEntityName();
                            break;
                        }
                    }
                }
                flag:
                while (dataClazz != null) {
                    Field[] fields = dataClazz.getDeclaredFields();
                    for (Field field : fields) {
                        if (headName.equalsIgnoreCase(field.getName())) {
                            String methodName = MethodUtils.setMethodName(field.getName());
                            Method method = dataClazz.getMethod(methodName, field.getType());
                            String value = null;
                            if (cell != null) {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                value = cell.getStringCellValue();
                            }
                            if (StringUtils.isEmpty(value)) {
                                break;
                            }
                            method.invoke(instance, convertType(field.getType(), value.trim()));
                            break flag;
                        }
                    }
                    dataClazz = (Class<T>) dataClazz.getSuperclass();
                }
            }
            beans.add(instance);
        }
        return beans;
    }


    public static List<String> readExcelForColumn(InputStream in, String fileNameSuffix, String sheetName) throws Exception {
        Workbook workbook = getWorkBoot(in, fileNameSuffix);
        List<String> result = new ArrayList<>();
        Sheet sheet = workbook.getSheet(sheetName);
        for (int i = 1; i < 4; i++) {
            Cell cell = sheet.getRow(i).getCell(1);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            result.add(value);
        }
        for (int i = 1; i < 10; i++) {
            Cell cell = sheet.getRow(i).getCell(3);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            result.add(value);
        }
        for (int i = 1; i < 11; i++) {
            Cell cell = sheet.getRow(i).getCell(4);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            result.add(value);
        }
        return result;
    }

    private static Object convertType(Class classzz, String value) {
        if (Integer.class == classzz || int.class == classzz) {
            return Integer.valueOf(value);
        }
        if (Short.class == classzz || short.class == classzz) {
            return Short.valueOf(value);
        }
        if (Byte.class == classzz || byte.class == classzz) {
            return Byte.valueOf(value);
        }
        if (Character.class == classzz || char.class == classzz) {
            return value.charAt(0);
        }
        if (Long.class == classzz || long.class == classzz) {
            return Long.valueOf(value);
        }
        if (Float.class == classzz || float.class == classzz) {
            return Float.valueOf(value);
        }
        if (Double.class == classzz || double.class == classzz) {
            return Double.valueOf(value);
        }
        if (Boolean.class == classzz || boolean.class == classzz) {
            return Boolean.valueOf(value.toLowerCase());
        }
        if (BigDecimal.class == classzz) {
            return new BigDecimal(value);
        }
        return value;
    }

    static class MethodUtils {
        private static final String SET_PREFIX = "set";
        private static final String GET_PREFIX = "get";

        private static String capitalize(String name) {
            if (name == null || name.length() == 0) {
                return name;
            }
            return name.substring(0, 1).toUpperCase() + name.substring(1);
        }

        public static String setMethodName(String propertyName) {
            return SET_PREFIX + capitalize(propertyName);
        }

        public static String getMethodName(String propertyName) {
            return GET_PREFIX + capitalize(propertyName);
        }
    }

}
